<!DOCTYPE html>
<html lang="zh-cn">
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <title>[mysql] mysql主从分离及负载均衡配置 - Never Give Up</title>
  <meta name="renderer" content="webkit" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>

<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />

<meta name="theme-color" content="#f8f5ec" />
<meta name="msapplication-navbutton-color" content="#f8f5ec">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="#f8f5ec">


<meta name="author" content="ZhangKQ" /><meta name="description" content="mysql主从分离及负载均衡配置 一.说明 1.前置条件 如果安装好了两个环境的mysql和nginx，那么你可以按照本文去配置mysql主从及负" /><meta name="keywords" content="数据库, mysql, 主从分离, 负载均衡" />






<meta name="generator" content="Hugo 0.92.0 with theme even" />


<link rel="canonical" href="https://blog.nevergiveup.tech/post/db/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%88%86%E7%A6%BB%E5%8F%8A%E8%B4%9F%E8%BD%BD%E5%9D%87%E8%A1%A1%E9%85%8D%E7%BD%AE/" />
<link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png">
<link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
<link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png">
<link rel="manifest" href="/manifest.json">
<link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5">



<link href="/sass/main.min.b5a744db6de49a86cadafb3b70f555ab443f83c307a483402259e94726b045ff.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.1.20/dist/jquery.fancybox.min.css" integrity="sha256-7TyXnr2YU040zfSP+rEcz29ggW4j56/ujTPwjMzyqFY=" crossorigin="anonymous">


<meta property="og:title" content="[mysql] mysql主从分离及负载均衡配置" />
<meta property="og:description" content="mysql主从分离及负载均衡配置 一.说明 1.前置条件 如果安装好了两个环境的mysql和nginx，那么你可以按照本文去配置mysql主从及负" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://blog.nevergiveup.tech/post/db/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%88%86%E7%A6%BB%E5%8F%8A%E8%B4%9F%E8%BD%BD%E5%9D%87%E8%A1%A1%E9%85%8D%E7%BD%AE/" /><meta property="article:section" content="post" />
<meta property="article:published_time" content="2017-05-10T15:37:56+08:00" />
<meta property="article:modified_time" content="2022-02-10T16:37:56+08:00" />

<meta itemprop="name" content="[mysql] mysql主从分离及负载均衡配置">
<meta itemprop="description" content="mysql主从分离及负载均衡配置 一.说明 1.前置条件 如果安装好了两个环境的mysql和nginx，那么你可以按照本文去配置mysql主从及负"><meta itemprop="datePublished" content="2017-05-10T15:37:56+08:00" />
<meta itemprop="dateModified" content="2022-02-10T16:37:56+08:00" />
<meta itemprop="wordCount" content="2102">
<meta itemprop="keywords" content="数据库,mysql,主从分离,负载均衡," /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="[mysql] mysql主从分离及负载均衡配置"/>
<meta name="twitter:description" content="mysql主从分离及负载均衡配置 一.说明 1.前置条件 如果安装好了两个环境的mysql和nginx，那么你可以按照本文去配置mysql主从及负"/>

<!--[if lte IE 9]>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
<![endif]-->

<!--[if lt IE 9]>
  <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
<![endif]-->

</head>
<body>
  <div id="mobile-navbar" class="mobile-navbar">
  <div class="mobile-header-logo">
    <a href="/" class="logo">Never Give Up</a>
  </div>
  <div class="mobile-navbar-icon">
    <span></span>
    <span></span>
    <span></span>
  </div>
</div>
<nav id="mobile-menu" class="mobile-menu slideout-menu">
  <ul class="mobile-menu-list">
    <a href="/">
        <li class="mobile-menu-item">主页</li>
      </a><a href="/post/">
        <li class="mobile-menu-item">归档</li>
      </a><a href="/tags/">
        <li class="mobile-menu-item">标签</li>
      </a><a href="/categories/">
        <li class="mobile-menu-item">分类</li>
      </a><a href="/remark/">
        <li class="mobile-menu-item">随言碎语</li>
      </a><a href="/about/">
        <li class="mobile-menu-item">关于</li>
      </a>
  </ul>

  


</nav>

  <div class="container" id="mobile-panel">
    <header id="header" class="header">
        <div class="logo-wrapper">
  <a href="/" class="logo">Never Give Up</a>
</div>





<nav class="site-navbar">
  <ul id="menu" class="menu">
    <li class="menu-item">
        <a class="menu-item-link" href="/">主页</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/post/">归档</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/tags/">标签</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/categories/">分类</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/remark/">随言碎语</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/about/">关于</a>
      </li>
  </ul>
</nav>

    </header>

    <main id="main" class="main">
      <div class="content-wrapper">
        <div id="content" class="content">
          <article class="post">
    
    <header class="post-header">
      <h1 class="post-title">[mysql] mysql主从分离及负载均衡配置</h1>

      <div class="post-meta">
        <span class="post-time"> 2017-05-10 </span>
        <div class="post-category">
            <a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"> 数据库 </a>
            </div>
          <span class="more-meta"> 约 2102 字 </span>
          <span class="more-meta"> 预计阅读 5 分钟 </span>
        
      </div>
    </header>

    <div class="post-toc" id="post-toc">
  <h2 class="post-toc-title">文章目录</h2>
  <div class="post-toc-content">
    <nav id="TableOfContents">
  <ul>
    <li><a href="#mysql主从分离及负载均衡配置">mysql主从分离及负载均衡配置</a>
      <ul>
        <li><a href="#一说明">一.说明</a>
          <ul>
            <li><a href="#1前置条件">1.前置条件</a></li>
            <li><a href="#2环境介绍">2.环境介绍</a></li>
          </ul>
        </li>
        <li><a href="#二主环境配置1921682043">二.主环境配置（192.168.20.43）</a>
          <ul>
            <li><a href="#1修改mysql的配置文件">1.修改mysql的配置文件</a></li>
            <li><a href="#2添加从库权限账号">2.添加从库权限账号</a></li>
            <li><a href="#3重启主服务器">3.重启主服务器</a></li>
            <li><a href="#4查看主服务器状态">4.查看主服务器状态</a></li>
            <li><a href="#5注意事项">5.注意事项</a></li>
          </ul>
        </li>
        <li><a href="#三从环境配置1921682044">三.从环境配置（192.168.20.44）</a>
          <ul>
            <li><a href="#1修改mysql的配置文件-1">1.修改mysql的配置文件</a></li>
            <li><a href="#2配置连接主服务器的信息">2.配置连接主服务器的信息</a></li>
            <li><a href="#3重启从服务器">3.重启从服务器</a></li>
            <li><a href="#4查看从服务器状态">4.查看从服务器状态</a></li>
            <li><a href="#5注意事项-1">5.注意事项</a></li>
          </ul>
        </li>
        <li><a href="#四faq">四.FAQ</a></li>
        <li><a href="#五mysql负载均衡1921682045">五.mysql负载均衡（192.168.20.45）</a>
          <ul>
            <li><a href="#1在nginxconf文本最后一行添加下述代码即可">1.在nginx.conf文本最后一行添加下述代码即可:</a></li>
            <li><a href="#2注意事项">2.注意事项</a></li>
          </ul>
        </li>
        <li><a href="#六测试">六.测试</a>
          <ul>
            <li><a href="#1主从复制测试">1.主从复制测试</a></li>
            <li><a href="#2负载均衡测试">2.负载均衡测试</a></li>
          </ul>
        </li>
      </ul>
    </li>
  </ul>
</nav>
  </div>
</div>
    <div class="post-content">
      <h1 id="mysql主从分离及负载均衡配置">mysql主从分离及负载均衡配置</h1>
<h2 id="一说明">一.说明</h2>
<h3 id="1前置条件">1.前置条件</h3>
<p>如果安装好了两个环境的mysql和nginx，那么你可以按照本文去配置mysql主从及负载均衡</p>
<ul>
<li>已安装mysql</li>
<li>已安装nginx</li>
</ul>
<h3 id="2环境介绍">2.环境介绍</h3>
<p>(主)环境 :linux(centos 7.0.2)   Mysql(5.7.22)  IP:192.168.20.43</p>
<p>(从)环境 :linux(centos 7.0.2)  Mysql(5.7.20)  IP:192.168.20.44</p>
<p>(均衡)环境 :linux(centos 7.0.2)  Mysql(5.7.20)  IP:192.168.20.45</p>
<h2 id="二主环境配置1921682043">二.主环境配置（192.168.20.43）</h2>
<h3 id="1修改mysql的配置文件">1.修改mysql的配置文件</h3>
<p>mysql通常为my.cnf和my.ini,我的配置文件路径是:/etc/my.cnf</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span><span class="lnt">8
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell"><span class="o">[</span>mysqld<span class="o">]</span>
log-bin<span class="o">=</span>mysql-bin    <span class="o">(</span>必须配置<span class="o">)</span>   启用二进制日志
server-id<span class="o">=</span><span class="m">1</span>          <span class="o">(</span>必须配置<span class="o">)</span>    为本台服务器配置唯一的Server ID
<span class="nv">auto_increment_offset</span> <span class="o">=</span> <span class="m">1</span>   <span class="c1">#这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突</span>
<span class="nv">auto_increment_increment</span> <span class="o">=</span> <span class="m">1</span>   <span class="c1">#这个参数一般用在主主同步中,用来错开自增值, 防止键值冲突</span>
<span class="nv">log_bin_trust_function_creators</span> <span class="o">=</span> <span class="m">1</span>  <span class="c1">#将函数复制到slave</span>
binlog-do-db<span class="o">=</span>test_sys  （可选配置）要同步的数据库名,要同步多个数据库,就多加几个replicate-db-db<span class="o">=</span>数据库名
binlog-ignore-db<span class="o">=</span>mysql （可选配置）//要忽略的数据库
</code></pre></td></tr></table>
</div>
</div><h3 id="2添加从库权限账号">2.添加从库权限账号</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell"><span class="c1">#进入mysql命令行</span>
create user as_test<span class="p">;</span>
<span class="c1">#GRANT REPLICATION SLAVE ON *.* TO &#39;as_test&#39;@&#39;192.168.20.166&#39; IDENTIFIED BY &#39;123456&#39;;</span>
grant replication slave,replication client on *.* to <span class="s1">&#39;slave&#39;</span>@<span class="s1">&#39;192.168.20.166&#39;</span> identified by <span class="s1">&#39;slpass&#39;</span><span class="p">;</span>

flush privileges<span class="p">;</span>      //刷新权限
</code></pre></td></tr></table>
</div>
</div><h3 id="3重启主服务器">3.重启主服务器</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell">/bin/systemctl restart mysqld.service<span class="o">(</span>centos<span class="o">)</span>
/bin/systemctl restart mysql.service<span class="o">(</span>ubuntu<span class="o">)</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="4查看主服务器状态">4.查看主服务器状态</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell"><span class="c1">#进入mysql命令行</span>
show master status<span class="se">\G</span><span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="5注意事项">5.注意事项</h3>
<ul>
<li>
<p>如果你不配置server-id或者配置值为0,那么主服务器将拒绝所有从服务器的连接。</p>
</li>
<li>
<p>在使用InnoDB的事务复制,为了尽可能持久和数据一致,你应该在my.cnf里配置</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell"><span class="nv">nnodb_flush_log_at_trx_commit</span><span class="o">=</span><span class="m">1</span> 和 <span class="nv">sync_binlog</span><span class="o">=</span><span class="m">1</span>
</code></pre></td></tr></table>
</div>
</div></li>
<li>
<p>确保主服务器里的skip-networking选项未启用,如果网络被禁用,你的从服务器将不能与主服务器通信并且复制失败</p>
</li>
</ul>
<h2 id="三从环境配置1921682044">三.从环境配置（192.168.20.44）</h2>
<h3 id="1修改mysql的配置文件-1">1.修改mysql的配置文件</h3>
<p>mysql通常为my.cnf和my.ini,我的配置文件路径是:/etc/my.cnf</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span><span class="lnt">8
</span><span class="lnt">9
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell"><span class="o">[</span>mysqld<span class="o">]</span>
log-bin<span class="o">=</span>mysql-bin    <span class="o">(</span>必须配置<span class="o">)</span>   启用二进制日志
server-id<span class="o">=</span><span class="m">2</span>          <span class="o">(</span>必须配置<span class="o">)</span>    为本台服务器配置唯一的Server ID
replicate-do-db<span class="o">=</span>test_sys  （可选配置）#在master端不指定binlog-do-db,在slave端用replication-do-db来过滤,要同步多个数据库,就多加几个replicate-db-db<span class="o">=</span>数据库名
binlog-ignore-db<span class="o">=</span>mysql （可选配置）//要忽略的数据库
<span class="c1">#relay-log=mysqld-relay-bin</span>
<span class="nv">relay_log</span> <span class="o">=</span> relay-log  <span class="c1">#开启中继日志</span>
<span class="nv">read_only</span> <span class="o">=</span> ON  <span class="c1">#只读开启,实现读写分离</span>
<span class="nv">super_read_only</span> <span class="o">=</span> <span class="m">1</span> <span class="c1">#root用户只读开启,实现读写分离</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="2配置连接主服务器的信息">2.配置连接主服务器的信息</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell"><span class="c1">#注意:MASTER_LOG_FILE和MASTER_LOG_POS的值要根据主数据库show master status中的File和 Position来修改</span>
<span class="c1">#change master to master_host=&#39;192.168.20.44&#39;,master_user=&#39;as_test&#39;,master_password=&#39;123456&#39;,master_log_file=&#39;mysql-bin.000001&#39;, master_log_pos=120;</span>
CHANGE MASTER TO <span class="nv">MASTER_HOST</span><span class="o">=</span><span class="s1">&#39;192.168.20.44&#39;</span>,MASTER_USER<span class="o">=</span><span class="s1">&#39;slave&#39;</span>,MASTER_PASSWORD<span class="o">=</span><span class="s1">&#39;slpass&#39;</span>,MASTER_LOG_FILE<span class="o">=</span><span class="s1">&#39;mysql-bin.000001&#39;</span>,MASTER_LOG_POS<span class="o">=</span>1981<span class="p">;</span>

flush privileges<span class="p">;</span>      //刷新权限
start slave<span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="3重启从服务器">3.重启从服务器</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell">/bin/systemctl restart mysqld.service<span class="o">(</span>centos<span class="o">)</span>
/bin/systemctl restart mysql.service<span class="o">(</span>ubuntu<span class="o">)</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="4查看从服务器状态">4.查看从服务器状态</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span><span class="lnt">8
</span><span class="lnt">9
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell">show slave status<span class="se">\G</span><span class="p">;</span>

Master_Log_File: mysql-bin.000006   <span class="c1">#复制的二进制文件</span>
Read_Master_Log_Pos: <span class="m">4</span>  <span class="c1">#从哪个pos位置开始复制</span>
Relay_Log_File: relay-log.000001
Relay_Log_Pos: <span class="m">4</span>
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No    <span class="c1">#复制功能还没有启动</span>
Slave_SQL_Running: No   <span class="c1">#复制功能还没有启动</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="5注意事项-1">5.注意事项</h3>
<ul>
<li>如果有多个从服务器,每个服务器的server-id不能重复,跟IP一样是唯一标识,如果你没设置server-id或者设置为0,则从服务器不会连接到主服务器</li>
<li>一般你不需要在从服务器上启用二进制日志,如果你在从服务器上启用二进制日志,那你可用它来做数据备份和崩溃恢复,或者做更复杂的事情（比如这个从服务器用来当作其它从服务器的主服务器）</li>
</ul>
<h2 id="四faq">四.FAQ</h2>
<p>在搭建mysql主主复制的时候,创建一个新的具有replicaion权限的新用户,刚开始的操作如下:</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell">mysql&gt; GRANT REPLICATION SLAVE ON test.* TO ‘server01’@’%’ IDENTIFIED BY ‘server01’<span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><p>会提示错误:ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES</p>
<p>错误的原因是因为,replication权限是一个全局权限,不可以指定在某一个库上,所以,把创建用户语句更改为如下即可:</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-shell" data-lang="shell">mysql&gt; GRANT REPLICATION SLAVE ON . TO ‘server01’@’192.168.40.129’ IDENTIFIED BY ‘568888’<span class="p">;</span>
Query OK, <span class="m">0</span> rows affected <span class="o">(</span>0.00 sec<span class="o">)</span>

ok,一切正常,但是,为了安全起见,在创建新的用户的时候,最好指定登陆地址,不然别人拿着你的账号就要干坏事儿了,虽然这个账号只具备replication权限,但是也是具有危险性的。所以,最后的语句更改为:

mysql&gt; GRANT REPLICATION SLAVE ON . TO ‘server01’@’192.168.40.129’ IDENTIFIED BY ‘568888’<span class="p">;</span>
Query OK, <span class="m">0</span> rows affected <span class="o">(</span>0.00 sec<span class="o">)</span>

mysql&gt; flush privileges<span class="p">;</span>
Query OK, <span class="m">0</span> rows affected <span class="o">(</span>0.01 sec<span class="o">)</span>
</code></pre></td></tr></table>
</div>
</div><h2 id="五mysql负载均衡1921682045">五.mysql负载均衡（192.168.20.45）</h2>
<h3 id="1在nginxconf文本最后一行添加下述代码即可">1.在nginx.conf文本最后一行添加下述代码即可:</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">stream {
    server {
       listen 3306;
       proxy_pass db;
    }
    upstream db {
       server 192.168.18.130:3305;
       server 192.168.18.129:3305;
    }
}
</code></pre></td></tr></table>
</div>
</div><h3 id="2注意事项">2.注意事项</h3>
<ul>
<li>
<p>读数据时，使用配置后的ip和端口，即读取数据时，从两个数据库读取；</p>
</li>
<li>
<p>写数据时，只用主库链接</p>
</li>
<li>
<p>mysql端口设置：</p>
<p>代码需要在nginx1.9版本以上才支持,上述配置中监听了3306端口,即3306端口将被nginx占用,而mysql的默认使用端口为3306,这将导致mysql无法启动,所以需要将mysql的端口号改为其他端口（本文中将其设置成3305,这将会在mysq主主同步的博文中说明）,proxy_pass即为代理地址</p>
</li>
</ul>
<h2 id="六测试">六.测试</h2>
<h3 id="1主从复制测试">1.主从复制测试</h3>
<ul>
<li>在主库写入数据，然后查看从库是否有数据</li>
</ul>
<h3 id="2负载均衡测试">2.负载均衡测试</h3>
<ul>
<li>在主库写入新数据</li>
<li>在从库修改刚刚写入的数据</li>
<li>使用192.168.20.45访问mysql数据库，每次刷新可以看到不同的数据</li>
</ul>

    </div>

    <div class="post-copyright">
  <p class="copyright-item">
    <span class="item-title">文章作者</span>
    <span class="item-content">ZhangKQ</span>
  </p>
  <p class="copyright-item">
    <span class="item-title">上次更新</span>
    <span class="item-content">
        2022-02-10
        
    </span>
  </p>
  
  
</div>
<div class="post-reward">
  <input type="checkbox" name="reward" id="reward" hidden />
  <label class="reward-button" for="reward">赞赏支持</label>
  <div class="qr-code">
    
    <label class="qr-code-image" for="reward">
        <img class="image" src="/qrcode/wechat-qr-code.jpg">
        <span>微信打赏</span>
      </label>
    <label class="qr-code-image" for="reward">
        <img class="image" src="/qrcode/alipay-qr-code.jpg">
        <span>支付宝打赏</span>
      </label>
  </div>
</div><footer class="post-footer">
      <div class="post-tags">
          <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
          <a href="/tags/mysql/">mysql</a>
          <a href="/tags/%E4%B8%BB%E4%BB%8E%E5%88%86%E7%A6%BB/">主从分离</a>
          <a href="/tags/%E8%B4%9F%E8%BD%BD%E5%9D%87%E8%A1%A1/">负载均衡</a>
          </div>
      <nav class="post-nav">
        <a class="prev" href="/post/db/mysql/mysql%E4%B8%AD%E9%97%B4%E4%BB%B6mycat%E5%AE%89%E8%A3%85%E5%8F%8A%E4%BD%BF%E7%94%A8/">
            <i class="iconfont icon-left"></i>
            <span class="prev-text nav-default">[mysql] mysql中间件MyCat安装及使用</span>
            <span class="prev-text nav-mobile">上一篇</span>
          </a>
        <a class="next" href="/post/editor/phpstrom%E5%B8%B8%E7%94%A8%E5%BF%AB%E6%8D%B7%E9%94%AE/">
            <span class="next-text nav-default">[phpstrom] phpstrom常用快捷键</span>
            <span class="next-text nav-mobile">下一篇</span>
            <i class="iconfont icon-right"></i>
          </a>
      </nav>
    </footer>
  </article>
        </div>
        

  

  

      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="social-links">
      <a href="mailto:wdyxzkq@163.com" class="iconfont icon-email" title="email"></a>
      <a href="https://github.com/dysoso" class="iconfont icon-github" title="github"></a>
      <a href="https://gitee.com/dysoso" class="iconfont icon-gitlab" title="gitlab"></a>
  <a href="https://blog.nevergiveup.tech/index.xml" type="application/rss+xml" class="iconfont icon-rss" title="rss"></a>
</div>

<div class="copyright">
  <span class="power-by">
    由 <a class="hexo-link" href="https://blog.nevergiveup.tech/">blog.nevergiveup.tech</a> 强力驱动
  </span>
  <span class="division">|</span>
  <span class="theme-info">
    主题 - 
    <a class="theme-link" href="https://github.com/olOwOlo/hugo-theme-even">Even</a>
  </span>

  

  <span class="copyright-year">
    &copy; 
    2021 - 
    2022<span class="heart"><i class="iconfont icon-heart"></i></span><span><a href="https://beian.miit.gov.cn/">蜀ICP备2021005948号-1</a></span>
  </span>
</div>

    </footer>

    <div class="back-to-top" id="back-to-top">
      <i class="iconfont icon-up"></i>
    </div>
  </div>
  
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.2.1/dist/jquery.min.js" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/slideout@1.0.1/dist/slideout.min.js" integrity="sha256-t+zJ/g8/KXIJMjSVQdnibt4dlaDxc9zXr/9oNPeWqdg=" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.1.20/dist/jquery.fancybox.min.js" integrity="sha256-XVLffZaxoWfGUEbdzuLi7pwaUJv1cecsQJQqGLe7axY=" crossorigin="anonymous"></script>



<script type="text/javascript" src="/js/main.min.c99b103c33d1539acf3025e1913697534542c4a5aa5af0ccc20475ed2863603b.js"></script>


<script type="application/javascript">
var doNotTrack = false;
if (!doNotTrack) {
	window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments)};ga.l=+new Date;
	ga('create', 'never-give-up', 'auto');
	ga('set', 'anonymizeIp', true);
	ga('send', 'pageview');
}
</script>
<script async src='https://www.google-analytics.com/analytics.js'></script>







</body>
</html>
